package com.cros.mysql;
/*
 * Licensed to the Apache Software Foundation (ASF) under one or more
 * contributor license agreements.  See the NOTICE file distributed with
 * this work for additional information regarding copyright ownership.
 * The ASF licenses this file to You under the Apache License, Version 2.0
 * (the "License"); you may not use this file except in compliance with
 * the License.  You may obtain a copy of the License at
 * 
 *      http://www.apache.org/licenses/LICENSE-2.0
 * 
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

import javax.sql.DataSource;

import org.apache.commons.dbcp.BasicDataSource;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.log4j.PropertyConfigurator;



import java.*;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
import java.util.Random;

//
// Here are the dbcp-specific classes.
// Note that they are only used in the setupDataSource
// method. In normal use, your classes interact
// only with the standard JDBC API


//
// Here's a simple example of how to use the BasicDataSource.
//

//
// Note that this example is very similiar to the PoolingDriver
// example.

//
// To compile this example, you'll want:
//  * commons-pool-1.5.6.jar
//  * commons-dbcp-1.3.jar (JDK 1.4-1.5) or commons-dbcp-1.4 (JDK 1.6+)
//  * j2ee.jar (for the javax.sql classes)
// in your classpath.
//
// To run this example, you'll want:
//  * commons-pool-1.5.6.jar
//  * commons-dbcp-1.3.jar (JDK 1.4-1.5) or commons-dbcp-1.4 (JDK 1.6+)
//  * j2ee.jar (for the javax.sql classes)
//  * the classes for your (underlying) JDBC driver
// in your classpath.
//
// Invoke the class using two arguments:
//  * the connect string for your underlying JDBC driver
//  * the query you'd like to execute
// You'll also want to ensure your underlying JDBC driver
// is registered.  You can use the "jdbc.drivers"
// property to do this.
//
// For example:
//  java -Djdbc.drivers=oracle.jdbc.driver.OracleDriver \
//       -classpath commons-pool-1.5.6.jar:commons-dbcp-1.4.jar:j2ee.jar:oracle-jdbc.jar:. \
//       PoolingDataSourceExample
//       "jdbc:oracle:thin:scott/tiger@myhost:1521:mysid"
//       "SELECT * FROM DUAL"
//
public class BasicDataSourceExample  extends Thread{
	public static  DataSource dataSource;
	public static int count=0;
	private static Log log = LogFactory.getLog(BasicDataSourceExample.class);
	
	public void run (){
		log.info("Inician el thread:"+Thread.currentThread());
		 //
        // Now, we can use JDBC DataSource as we normally would.
        //
        Connection conn = null;
        Statement stmt = null;
        ResultSet rset = null;
        PreparedStatement pstmt = null;
        StringBuilder sqlSelect= new StringBuilder("Select t_vales.VALE,t_vales.IDVALES,t_vales.IDCODIGODEESTATUSVALES,t_vales.FECHAREGISTRO,t_vales.MONTO,t_vales.IDREGISTRO,t_vales.IDEMISORA ").append(
        				 "FROM t_vales WHERE t_vales.IDCODIGODEESTATUSVALES = 0 AND t_vales.VALE IN ('22109003094556010501' ").append(
        				 ", '22109003094657010201' , '21109063885751010401' , '21109063885852010101' , '00158472111232004243' ").append(
        				 ", '00158473111235006067' , '39449126111235013393' , '39449127111238015216' , '31174821111248012238' ").append(
                         ", '22109001682649010201' , '22109004494356010401' , '22109004494255010701' , '22109001682558010501' ").append(
                         ", '22109001682457010901' , '31175225111252019462' , '28780952111242007182' , '00798696111246012962' ").append(
                         ", '38395766111232011588' , '31189248111251024999' , '31170047111255023052' , '39445321111230004278' ").append(
                         ", '39445320111237002454' , '28779859111243019995' , '31173859111257026819' , '3207554003282739' ").append(
                         ", '3205057002787768' , '3205050002787757' , '3201055002773889' , '3200554002763889' , '39280342111235005083' ").append(
                         ", '39714561111244007329' , '39714560111241005505' , '28993589111251021977' , '3201056003197081' ").append(
                         ", '3205053003251089' , '3207552003305088' , '3200551003143080' , '3910049120560432' , '3910046120560442' ").append(
                         ", '3910043120560451' , '31187510111239010411' , '39727619111240021888' , '39456628111241017063' ").append(
                         ", '3202050004595130' , '3202050004595131' , '3202050004595132' , '3202050004595133' , '3202050004595134' , '3202050004595135' , '3202050004595136' , '3202050004595137' , '3202050004595138' , '3202050004595139' , '3202050004595230' , '3202050004595530' , '3202050004595530') ");
        String sqlInsert="INSERT INTO T_REGISTRO(NUMERO_DE_TIENDA,NUMERO_DE_CAJA,FECHA_CONTABLE_DE_CAJA,NUMERO_DE_TICKET,NUMERO_DE_EMPLEADO,NUMEROS_DE_VALES,ID_TIPO_REGISTRO) values(57934,52325,'08-04-2013 07:23:51',35487,24403,7,1)";
        int emi=1;
        for(int i=0;i<1;i++){
        try {
        	
        	conn = dataSource.getConnection();
        	pstmt = conn.prepareStatement(sqlSelect.toString());
        	rset = pstmt.executeQuery();
	    	while ( rset.next() ){
	    		//if(ini_load.getDebug_c()){
	    		 //logger.info("Dentro del While que obtiene los vales duplicados");
	    		//}
	    		
	    		//log.info("Dentro del While que obtiene los vales duplicados");
		    	
		    	log.info(rset.getString("idvales"));
		    	log.info(rset.getString("vale"));
		    	log.info(rset.getString("idcodigodeestatusvales"));
		    	log.info(rset.getString("fecharegistro"));
		    	log.info(rset.getString("monto"));
		    	log.info(rset.getString("idregistro"));
		    	log.info(rset.getString("idemisora"));
		    
		    }
        	
            /*pstmt = conn.prepareStatement(sqlInsert,PreparedStatement.RETURN_GENERATED_KEYS);
            pstmt.executeUpdate();
            rset 	= pstmt.getGeneratedKeys(); 
        	int res = -1;
    		if(rset.next()){
          		res = rset.getInt(1);
          		log.info("Clave generada registro= " + res);
    		}*/
    		if(emi>5){
    			emi=1;
    		}
    		
    		/*
    		String vale=fnVoucherEmitter(emi);
    		
    		StringBuilder dos = new StringBuilder().append("INSERT INTO T_VALES(vale,idcodigodeestatusvales,fecharegistro,monto,idregistro,idemisora) values('").append(vale).append("',0,CURRENT_TIMESTAMP,'$ 100.00',").append(res).append(",").append(emi).append(")");
    		pstmt = conn.prepareStatement(dos.toString(),PreparedStatement.RETURN_GENERATED_KEYS);
          	pstmt.executeUpdate();
          	rset 	= pstmt.getGeneratedKeys(); 
          	emi++;
          	res = -1;
    		if(rset.next()){
    			res = rset.getInt(1);
          		log.info("Clave generada vale= " + res);
    		}
    		*/
    		
    		
          	 
    		
    		
    		log.info("Clave generada vale= " + count++);
    	
    	
        } catch(SQLException e) {
        	log.info("Error:" + e);
            //e.printStackTrace();
        } finally {
        	log.info("Termina el thread:"+Thread.currentThread());
            try { if (rset != null )	rset.close(); 	} catch(SQLException e) {	e.printStackTrace();	}
            try { if (stmt != null ) 	stmt.close(); 	} catch(SQLException e) {	e.printStackTrace();	}
            try { if (pstmt != null)	pstmt.close(); 	} catch(SQLException e) {	e.printStackTrace();	}
            try { if (conn != null ) 	conn.close(); 	} catch(SQLException e) { 	e.printStackTrace();	}
        }
        }
		
		
	}

    public static void main(String[] args) {
        // First we set up the BasicDataSource.
        // Normally this would be handled auto-magically by
        // an external configuration, but in this example we'll
        // do it manually.
        //
    	
    	//Leer el properties el log4j
    	
    	try {
			FileInputStream filexml = new FileInputStream("./log4j.properties");
			Properties properties = new Properties();
			try {
				properties.load(filexml);
			} catch (IOException e) {
				e.printStackTrace();
			}
			
			PropertyConfigurator.configure(properties);
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		}
    	
    	
        log.info("Setting up data source.");
        String url="jdbc:sqlserver://192.168.0.130:1433;DatabaseName=recordaseval";
        
        
        dataSource = setupDataSource(url);
       
        for(int i=0;i<5000;i++){
        	Thread thread = new BasicDataSourceExample();
        	thread.start();
        }
       
        log.info("Donee.");
        
    }

    public static DataSource setupDataSource(String connectURI) {
        BasicDataSource ds = new BasicDataSource();
        ds.setDriverClassName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        ds.setUsername("aseval2");
        ds.setPassword("sqlserver2008$");
        ds.setUrl(connectURI);
        ds.setMaxActive(5000);
        ds.setInitialSize(5000);
        ds.setValidationQuery("select 1");
        return ds;
    }

    public static void printDataSourceStats(DataSource ds) {
        BasicDataSource bds = (BasicDataSource) ds;
        log.info("NumActive: " + bds.getNumActive());
        log.info("NumIdle: " + bds.getNumIdle());
    }

    public static void shutdownDataSource(DataSource ds) throws SQLException {
        BasicDataSource bds = (BasicDataSource) ds;
        bds.close();
    }
    
    
    private static String fnVoucherEmitter(int emitter){
    	String vale = null;
    	switch(emitter)
    	{
    	case 1: vale = new StringBuffer().append(fnCreaVales()).append(fnCreaVales()).append(fnCreaVales()).append(fnCreaVales()).toString();
    	        vale = new StringBuffer().append("1").append(vale.substring(1, vale.length())).toString();
    	        break;
    	case 2: vale = new StringBuffer().append(fnCreaVales()).append(fnCreaVales()).append(fnCreaVales()).append(fnCreaVales()).append(fnCreaVales()).toString();
       			vale = new StringBuffer().append("2").append(vale.substring(1, vale.length()-1)).append("1").toString();
       			break;
    	case 3: vale = new StringBuffer().append(fnCreaVales()).append(fnCreaVales()).append(fnCreaVales()).append(fnCreaVales()).toString();
        		vale = new StringBuffer().append("3").append(vale.substring(1, vale.length())).toString();
        		break;
    	case 4: vale = new StringBuffer().append(fnCreaVales()).append(fnCreaVales()).append(fnCreaVales()).append(fnCreaVales()).append(fnCreaVales()).append(fnCreaVales()).toString();
        		vale = new StringBuffer("4").append(vale.substring(1, vale.length())).toString();
        		break;
    	  default:
    		  	vale = new StringBuffer().append(fnCreaVales()).append(fnCreaVales()).append(fnCreaVales()).append(fnCreaVales()).append(fnCreaVales()).toString();
    		    break;
    	}
    	
    	return vale;
    }
    
	private static String fnCreaVales() {
		Random ran_num = null;
		ran_num = new Random();
		long num_aux = 0;
		num_aux = ran_num.nextInt(9999);
		return fnFill(num_aux);
	}
	
	private static String fnFill(long value_input){
		String resultado = null;
		
		resultado = String.valueOf(value_input);
		
		if(resultado.length() < 4){
		 int limite = 4 - resultado.length();
		
		 resultado = "";
		 for (int i = 0;i < limite; i++){
			resultado +="0";
		 }

		 //log.info(resultado+value_input);
		 
		 return resultado+value_input;
		}else{
			resultado = resultado.substring(0, 4);
			//log.info(resultado);
			return resultado;
		}
		
	}
}